package com.jd.sunny.jdwork;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;
import javax.script.ScriptEngineManager;
import javax.script.ScriptException;

import com.jd.sunny.util.DBConnectionUtil;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

public class ExcelHandle {
	
	 public static Connection connection = DBConnectionUtil.getConnection();
	 
	/**
	 * 获取工作表
	 * @param fileName
	 * @param sheetNumber
	 * @return
	 * @throws java.io.IOException
	 */
	public HSSFSheet getHSSFSheet(String fileName,int sheetNumber) throws IOException{
		//用HSSF的usermodel读取文件很简单。
		//首先创建一个InputStream
		//然后创建一个HSSFWorkbook
		//然后得到一个HSSFSheet
		InputStream myxls = new FileInputStream(fileName);	
		HSSFWorkbook hssfWordbook   = new HSSFWorkbook(myxls);
		HSSFSheet hssfSheet = hssfWordbook.getSheetAt(sheetNumber);
		return hssfSheet;
	}
	
	/**
	 * 获取所有行的iterator
	 * @param fileName
	 * @param sheetNumber
	 * @return
	 * @throws java.io.IOException
	 */
	public Iterator getRowsIterator(String fileName,int sheetNumber) throws IOException{
		//用HSSF的usermodel读取文件很简单。
		//首先创建一个InputStream
		//然后创建一个HSSFWorkbook
		//然后得到一个HSSFSheet
		InputStream myxls = new FileInputStream(fileName);	
		HSSFWorkbook hssfWordbook   = new HSSFWorkbook(myxls);
		HSSFSheet hssfSheet = hssfWordbook.getSheetAt(sheetNumber);
		Iterator<Row> rowsIterator = hssfSheet.iterator();
		return rowsIterator;
	}

//	/**
//	 * 计算处理表格
//	 * @param args
//	 * @throws java.io.IOException
//	 * @throws javax.script.ScriptException
//	 */
//	public static void calcExcel(ExcelParametes args) throws IOException, ScriptException {
//		String source = args.getSource();
//		String target =  args.getTarget();
//		int sheetNumber =   args.getSheet();
//		Map<Integer,String> map =  args.getMap();
//
//		POIFSFileSystem fs =  new POIFSFileSystem(new FileInputStream(source));
//		HSSFWorkbook wb = new HSSFWorkbook(fs);
//		HSSFSheet sheet = wb.getSheetAt(sheetNumber);
//		Iterator<Row> iterator = sheet.iterator();
//
//		while(iterator.hasNext()){
//			Row row = iterator.next();
//			//去掉第一行
//			if(row.getRowNum()==0){
//				continue;
//			}
//			//解析字符串,#代表列,未列的不修改,列中有空值或者字符串的不处理
//			Set<Integer> key  =  map.keySet();
//			Iterator<Integer> keyIterator = key.iterator();
//			while(keyIterator.hasNext()){
//				Integer targetColumn =  keyIterator.next();
//				String expression =  map.get(targetColumn);
//				if (expression!=null) {
//					int leftBracket = -1;
//					int rightBracket = -1;
//					while((leftBracket = expression.indexOf("["))!= -1 && (rightBracket = expression.indexOf("]"))!= -1){
//						//获得旧的字符串
//						String oldChar =  expression.substring(leftBracket, rightBracket + 1);
//						//获得列
//						String parameterColum = expression.substring(leftBracket + 1,rightBracket);
//						Cell cell = row.getCell(Integer.parseInt(parameterColum));
//						String newChar = getCellStringValue(cell);
//						//注意此处替换[]的方法
//						expression = expression.replaceAll("\\[" + oldChar + "\\]", newChar);
//					}
//				}
//				//运行表达式，修改Excel
//				ScriptEngineManager scriptEngineManager = new ScriptEngineManager();
//				double result = (Double) scriptEngineManager.getEngineByName("javascript").eval(expression);
//				Cell targetCell = row.getCell(targetColumn);
//				if(targetCell == null){
//					targetCell = row.createCell(targetColumn);
//				}
//				targetCell.setCellType(Cell.CELL_TYPE_NUMERIC);
//				targetCell.setCellValue(result);
//			}
//		}
//
//		//写文件
//		FileOutputStream fileOut = new FileOutputStream(target);
//		wb.write(fileOut);
//		fileOut.close();
//	}
//
//	/**
//	 *  根据excel生成sql语句
//	 * @param args
//	 * @throws java.io.IOException
//	 * @throws javax.script.ScriptException
//	 * @throws java.sql.SQLException
//	 */
//	public static void generateSql(ExcelParametes args) throws IOException, ScriptException, SQLException {
//		//根据表格先计算好数值，形成excel，然后调用此方法形成sql语句
//		//调用一次此方法，只会操作一个sheet工作表，只会生成一种sql语句，只会操作同一个table
//		//关联表要先插入数据库，可以查询
//		Statement statement =  connection.createStatement();
//		String source = args.getSource();
//		String target =  args.getTarget();
//		int sheetNumber =   args.getSheet();
//		String originalSql = args.getSql();
//		Map<String,SqlParametes> map =  args.getMap();
//
//		POIFSFileSystem fs =  new POIFSFileSystem(new FileInputStream(source));
//		HSSFWorkbook wb = new HSSFWorkbook(fs);
//		HSSFSheet sheet = wb.getSheetAt(sheetNumber);
//		Iterator<Row> iteratorRow = sheet.iterator();
//
//		StringBuffer sqlStringBuffer = new StringBuffer();
//		while(iteratorRow.hasNext()){
//			Row row = iteratorRow.next();
//			//去掉第一行
//			if(row.getRowNum()==0){
//				continue;
//			}
//
//			//解析字符串，[]方括号中的数字和字母区分开,得出数据插入sql语句中
//			//originalSql赋值给sql，下次还可以重新替换
//			String sql = originalSql;
//			if (sql!=null) {
//				int leftBracket = -1;
//				int rightBracket = -1;
//				while((leftBracket = sql.indexOf("["))!= -1 && (rightBracket = sql.indexOf("]"))!= -1){
//					//获得旧的字符串,和不带[]的字符串key,得到对应 的SqlParametes
//					String oldChar =  sql.substring(leftBracket, rightBracket + 1);
//					String key = sql.substring(leftBracket + 1,rightBracket);
//					SqlParametes sqlArgs =  map.get(key);
//					int excelColumn  = sqlArgs.getExcelColumn();
//					String dbTable = sqlArgs.getDbTable();
//					String dbType  = sqlArgs.getDbType();
//					String queryColumn =  sqlArgs.getQueryColumn();
//					String relateColumn = sqlArgs.getRelateColumn();
//
//					//从excel中取出数据
//					Cell cell = row.getCell(excelColumn);
//					String cellValue = getCellStringValue(cell);
//					System.out.println(cellValue);
//					//SqlParametes 中有没有关联的数据库
//					if ("" == dbTable || null ==  dbTable || dbTable.length() <=0) {
//						//没有关联的数据库,直接从excel中取值替换
//						//判断类型，觉得是否在sql语句中加上''
//						String newChar = "";
//						if("integer".equalsIgnoreCase(dbType) || "NUMBER".equalsIgnoreCase(dbType)){
//							newChar = cellValue;
//						}else if ("varchar".equalsIgnoreCase(dbType) || "varchar2".equalsIgnoreCase(dbType)  || "nvarchar2".equalsIgnoreCase(dbType)) {
//							newChar = "'" + cellValue + "'";
//						}
//						sql = sql.replace(oldChar, newChar);
//					}else if ("" != dbTable && null !=dbTable&& dbTable.length() >0) {
//						Object object = null;
//						//有关联数据库，就需要查询数据库
//						String relateColumnType = getColumnType(dbTable,relateColumn);
//						String temp_value = "";
//						if("integer".equalsIgnoreCase(relateColumnType) || "NUMBER".equalsIgnoreCase(relateColumnType)){
//							//如果类型是数字，查询就行
//							temp_value = cellValue;
//						}else if ("varchar".equalsIgnoreCase(relateColumnType) || "varchar2".equalsIgnoreCase(relateColumnType)  || "nvarchar2".equalsIgnoreCase(relateColumnType)) {
//							//如果是字符串类型，查询需要加上  ''
//							temp_value = "'" + cellValue + "'" ;
//						}
//						PreparedStatement preparedStatement = connection.prepareStatement("select " + queryColumn + " from " + dbTable +" where " + relateColumn + " = " + temp_value);
//						ResultSet resultSet = preparedStatement.executeQuery();
//						while(resultSet.next()){
//							object =  resultSet.getObject(1);
//						}
//						resultSet.close();
//						preparedStatement.close();
//
//						//查出数据，替换sql语句
//						// 返回值转换成所要存储的数据的类型
//						if("integer".equalsIgnoreCase(dbType) || "NUMBER".equalsIgnoreCase(dbType)){
//							//如果类型是数字，查询就行
//							temp_value = "" +  (Long)object;
//						}else if ("varchar".equalsIgnoreCase(dbType) || "varchar2".equalsIgnoreCase(dbType)  || "nvarchar2".equalsIgnoreCase(dbType)) {
//							//如果是字符串类型，查询需要加上  ''
//							temp_value = "'" + (String)object + "'" ;
//						}
//						//将[]中的字符串替换成数据库中查询出的数据
//						sql = sql.replace( oldChar , temp_value);
//					}
//				}
//			}
//			sqlStringBuffer.append(sql + "\n");
//		}
//		statement.close();
//		FileUtil.writeString(new File(target), sqlStringBuffer.toString());
//	}
	//根据cell得到值，结果为long
	public static long getCellLongValue(Cell cell) {
		long result = 0l;
		if(cell!=null){
			int type = cell.getCellType();
			if (type == Cell.CELL_TYPE_NUMERIC) {
				result = (long)( cell.getNumericCellValue());
			}else if (type == Cell.CELL_TYPE_STRING) {
				result = Long.parseLong(cell.getStringCellValue());
			}
		}
		return result;
	}
	
	//根据cell得到值，结果为String
	public static String getCellStringValue(Cell cell) {
		String result = "";
		if(cell!=null){
			int type = cell.getCellType();
			if (type == Cell.CELL_TYPE_NUMERIC) {
				result = String.valueOf(((long) cell.getNumericCellValue()));
			}else if (type == Cell.CELL_TYPE_STRING) {
				result =cell.getStringCellValue();
			}
		}
		return result;
	}

	public static String getColumnType(String dbTable,String column) throws SQLException {
		Statement statement =  connection.createStatement();
		ResultSet resultSet = statement.executeQuery("select data_type from user_tab_columns where table_name = '" + dbTable.toUpperCase() + "' and column_name = '" + column + "'");
		String data_type = "";
		while (resultSet.next()) {
			data_type =  resultSet.getString("data_type");		
		}
		return data_type;
	}
		
}
